import click
import os
import platform
import sys
from collections import OrderedDict

import json
import pandas as pd
from common.database import SessionLocal

from app.bill.utils.bill_sync_utils import BillSyncUtils
from app.bill.handlers.bill_analysis_handler import BillAnalysisHandler
from app.bill.handlers.bill_month_handler import BillMonthHandler
from app.bill.handlers.bill_event_handler import BillEventHandler
from app.bill.handlers.oa_asset_handler import OaAssetHandler

from app.bill.models.tag_department import TagDepartment
from app.bill.models.bill_instance import BillInstance
from app.bill.models.bill_analysis import BillAnalysis
# from setting import config

from core.logger import logger


class BillClick(object):
    def __init__(self):
        self.session = SessionLocal()


    def __del__(self):
        self.session.close()


    def bill_sync_from_aliyun(self, bill_cycle):
        bill_sync_utils = BillSyncUtils()
        bill_analysis_handler = BillAnalysisHandler()
        bill_month_handler = BillMonthHandler()

        if "-" in bill_cycle:
            bill_cycles = [bill_cycle]
        else:
            bill_cycles = bill_month_handler.get_months(bill_cycle)

        # 更新账单数据
        for cycle in bill_cycles:
            result = bill_sync_utils.update_bill_instance(self.session, cycle)
            if not result:
                logger.error(f"update bill instance failed, cycle:{cycle}")
                return False
            logger.info(f"update bill_instance success, cycle:{cycle}")


        # 对比一个月的差异账单，并写入数据
        for cycle in bill_cycles:
            result = bill_analysis_handler.month_on_month(self.session, cycle)
            if not result:
                logger.error(f"update analysis failed, cycle:{cycle}")
                return False
            logger.info(f"update analysis success, cycle:{cycle}")

        # 同步月份信息表
        for cycle in bill_cycles:
            result = bill_month_handler.update_from_instance(self.session, cycle)
            if not result:
                logger.error(f"update month_bill failed, cycle:{cycle}")
                return False
            logger.info(f"update month_bill success, cycle:{cycle}")

        logger.info("sync bill from aliyun success")




    def sync_asset_from_oa(self, month:str):
        oa_asset_handler = OaAssetHandler()
        oa_asset_handler.task_update_from_work_order_handle(self.session, month)


def main():
    bill_click = BillClick()
    bill_click.bill_sync_from_aliyun("2023-08")
    # bill_click.sync_asset_from_oa("2023-10")
    
    
    
    # bill_click.bill_event_from_work_order("2023-3")
    # bill_click.export_tag_department_to_excel()
    # bill_click.update_tag_department_from_excel("tag_department信息.xlsx")

    # bill_click.bill_analysis("2022-03")
    # bill_click.bill_group_by_department("2022-06")
    # bill_click.bill_analysis_report("2023")
    # print("aaa")

    # bill_click.update_tag_department_from_excel("/home/boll/Documents/gitlab-code/mzhuang/ops-be/tag_department信息.xlsx")


if __name__ == "__main__":
    main()
    







    # def export_tag_department_to_excel(self):
    #     try:
    #         tag_departments_info = TagDepartment.get_entries(self.session)
    #         df = pd.DataFrame(tag_departments_info)
    #         df.to_excel(f"tag_department信息.xlsx", index=False)
    #         logger.info("write info to_excel success")
    #     except Exception as e:
    #         logger.exception(e)
    #         return False

    #     logger.info("export tag department to excel success")
    #     return True


    # def update_tag_department_from_excel(self, file_path: str):
    #     try:
    #         tag_departments_info = []
    #         df = pd.read_excel(file_path, "Sheet1")
    #         for i in df.index.values:
    #             row_data = df.loc[i].to_dict()
    #             row_data["belong_department"] = json.loads(row_data["belong_department"])
    #             if "update_at" in row_data: del row_data["update_at"]
    #             tag_departments_info.append(row_data)

    #         for data in tag_departments_info:
    #             tag_department_object = TagDepartment.get_object_by_tag(self.session, data["tag_key"], data["tag_value"])
    #             if tag_department_object:
    #                 # 如果存在, 则更新
    #                 data["uuid"] = tag_department_object.uuid
    #                 result = TagDepartment.update(self.session, data)
    #                 if not result:
    #                     logger.error(f"update tag department to database failed")
    #                     return False
    #                 logger.info(f"{data['tag_key']}:{data['tag_value']} was exist, update success")
    #                 continue

    #             result = TagDepartment.add(self.session, data)
    #             if not result:
    #                 logger.error(f"add tag department to database failed")
    #                 return False
    #             logger.info(f"{data['tag_key']}:{data['tag_value']} add success")
    #     except Exception as e:
    #         logger.exception(e)
    #         return False

    #     logger.info("update tag department from excel success")
    #     return True


    # def bill_analysis(self, bill_cycle):
    #     # 对比一个月的差异账单，并写入数据
    #     bill_analysis_handler = BillAnalysisHandler()
    #     bill_analysis_handler.month_on_month(self.session, bill_cycle)

    #     # 打印差异账单数据，方便oa提单
    #     result = bill_analysis_handler.get_text_information(self.session, bill_cycle)
    #     print(result["text"])

    #     # 将这个月差异的账单数据导出到execl
    #     bill_analysis_handler.get_departments_to_excel(self.session, bill_cycle, result["departments"])

    #     logger.info(f"update analysis:{bill_cycle} success")


    # def bill_group_by_department(self, bill_cycle):
    #     # 输出excel文档给给boll做预算
    #     bills = BillInstance.get_all_instance_bill(self.session, bill_cycle)

    #     # 详细账单导出到excel
    #     excel_writer = pd.ExcelWriter(f"部门费用{bill_cycle}.xlsx")
    #     df_bill = pd.DataFrame(bills)
    #     df_bill.to_excel(excel_writer, sheet_name="详细账单", index=False)

    #     # 统计每个管理部门的费用
    #     group_data = df_bill.groupby(["maintain_department"]).agg({"pretax_amount": "sum"}).to_dict()
    #     department_data = [
    #         {
    #             "管理部门": department,
    #             "费用": round(amount)
    #         }
    #         for department, amount in group_data["pretax_amount"].items()
    #     ]
    #     df_department = pd.DataFrame(department_data)
    #     df_department.to_excel(excel_writer, sheet_name="管理部门费用", index=False)

    #     # 统计每个项目的费用
    #     group_data = df_bill.groupby(["belong_department"]).agg({"pretax_amount": "sum"}).to_dict()
    #     project_data = [
    #         {
    #             "所属部门": project,
    #             "费用": round(amount)
    #         }
    #         for project, amount in group_data["pretax_amount"].items()
    #     ]
    #     df_project = pd.DataFrame(project_data)
    #     df_project.to_excel(excel_writer, sheet_name="所属部门费用", index=False)
    #     excel_writer.close()
    #     logger.info("export by department success")
    #     return True


    # def get_work_order_project(self, instance_id):
    #     yugong_ids = [
    #         "i-wz9c58cw570k2thv70sb",
    #         "i-wz92g33obkw3bc8ppq5k",
    #         "i-wz95462bnb5qwrt7gmx6",
    #         "i-wz9c58cw570k2thv70sb",
    #         "i-wz92g33obkw3bc8ppq5k",
    #         "i-wz95462bnb5qwrt7gmx6",
    #         "i-wz9c58cw570k2thv70sb",
    #         "i-wz92g33obkw3bc8ppq5k",
    #         "i-wz95462bnb5qwrt7gmx6",
    #         "i-wz9fqkpbq59i14y8kzcm",
    #         "i-wz9ah7jlsi1f2qv2mvrj",
    #         "i-wz91r47g87osh1yx642i",
    #         "i-wz926b9ugek9edci3azo",
    #         "i-wz9dev0583p8o2tqx137",
    #         "i-wz967ax5d1y1y98bqsp3",
    #         "i-wz9fx40li12g5h3c1j27",
    #         "i-wz9dev0583p8o2tqx13a",
    #         "i-wz9emxfzl6ef5mvpk3wq",
    #         "i-wz93do14wxqs1l090jcj",
    #         "i-wz9emxfzl6ef5mvpk3ws",
    #         "i-wz9fx40li12g5l1e9n16",
    #         "i-wz94gb5cfw921wyrenwv",
    #         "i-wz9c58cw570k2thv70sb",
    #         "i-wz92g33obkw3bc8ppq5k",
    #         "i-wz95462bnb5qwrt7gmx6",
    #         "i-wz96rap9urpfvyh3brko",
    #         "i-wz97c3p7jior1v241ijz",
    #         "i-wz9ba5j3rnh6nv739ou1",
    #         "i-wz9c58cw570k2thv70sb",
    #         "i-wz92g33obkw3bc8ppq5k",
    #         "i-wz95462bnb5qwrt7gmx6",
    #         "i-wz9c58cw570k2thv70sb",
    #         "i-wz92g33obkw3bc8ppq5k",
    #         "i-wz95462bnb5qwrt7gmx6",
    #         "i-wz94z29cltebxxxwqsro",
    #         "i-wz90yxkshsdpaya579wd",
    #         "i-wz95cvopijnty35evzvj",
    #         "i-wz9dev0583p8o2tqx137",
    #         "i-wz967ax5d1y1y98bqsp3",
    #         "i-wz9fx40li12g5h3c1j27",
    #         "i-wz9dev0583p8o2tqx13a",
    #         "i-wz9emxfzl6ef5mvpk3wq",
    #         "i-wz93do14wxqs1l090jcj",
    #         "i-wz9emxfzl6ef5mvpk3ws",
    #         "i-wz9fx40li12g5l1e9n16",
    #         "i-wz94gb5cfw921wyrenwv",
    #         "i-wz9fqkpbq59i14y8kzcm",
    #         "i-wz9ah7jlsi1f2qv2mvrj",
    #         "i-wz91r47g87osh1yx642i",
    #         "i-wz9fqkpbq59i14y8kzcm",
    #         "i-wz9ah7jlsi1f2qv2mvrj",
    #         "i-wz91r47g87osh1yx642i",
    #     ]
    #     if instance_id in yugong_ids:
    #         return "bi升级项目"

    #     return "其他项目"



    # def get_diff_data(self, current_infos, last_infos, key, key2):
    #     for current_info in current_infos:
            
    #         for last_info in last_infos:
    #             if last_info[key] == current_info[key]:
    #                 current_info["diff"] = current_info[key2] - last_info[key2]
    #                 break
    #         else:
    #             current_info["diff"] = - current_info[key2]
            
    # def bill_analysis_report(self, bill_year):
    #     bill_month_handler = BillMonthHandler()
    #     bill_cycles = bill_month_handler.get_months(bill_year)

    #     # 差异账单
    #     excel_writer = pd.ExcelWriter(f"{bill_year}分析报告.xlsx")
    #     analysis_datas = []
    #     for bill_cycle in bill_cycles:
    #         analysis_info = BillAnalysis.get_analysis_search(self.session, bill_cycle=bill_cycle, page=1, per_page=10000, field="all", value="")
    #         analysis_datas.extend(analysis_info["items"])
    #     # for analysis_data in analysis_datas:
    #     #     analysis_data["项目"] = self.get_work_order_project(analysis_data["instance_id"])
    #     df_analysis = pd.DataFrame(analysis_datas)
    #     df_analysis.to_excel(excel_writer, sheet_name="变更账单详情", index=False)

    #     # # 总金额
    #     # bill_sum_datas = []
    #     # for bill_cycle in bill_cycles:
    #     #     bill_sum_info = BillInstance.get_instance_sum_for_month(self.session, bill_cycle=bill_cycle)
    #     #     bill_sum_datas.append(bill_sum_info)
    #     # df_bill_sum = pd.DataFrame(bill_sum_datas)
    #     # df_bill_sum.to_excel(excel_writer, sheet_name="账单总费用", index=False)

    #     ########################################################
    #     # 按环境聚合归类总费用
    #     bill_class_sum_datas = []
    #     last_infos = []
    #     for bill_cycle in bill_cycles:
    #         bill_class_sum_info = BillInstance.get_instance_aggregate_class_env(self.session, bill_cycle=bill_cycle)
    #         self.get_diff_data(bill_class_sum_info, last_infos, "environment", "pretax_amount")
    #         bill_class_sum_datas.extend(bill_class_sum_info)
    #         last_infos = bill_class_sum_info
    #     df_bill_class_sum = pd.DataFrame(bill_class_sum_datas)
    #     df_bill_class_sum.to_excel(excel_writer, sheet_name="环境归类聚合", index=False)

    #     # 部门总费用聚合
    #     bill_department_sum_datas = []
    #     last_infos = []
    #     for bill_cycle in bill_cycles:
    #         bill_department_sum_info = BillInstance.get_instance_aggregate(self.session, bill_cycle=bill_cycle, field="belong_department", page=1, per_page=1000)
    #         self.get_diff_data(bill_department_sum_info["items"], last_infos, "belong_department", "pretax_amount")
    #         bill_department_sum_datas.extend(bill_department_sum_info["items"])
    #         last_infos = bill_department_sum_info["items"]
    #     df_bill_department_sum = pd.DataFrame(bill_department_sum_datas)
    #     df_bill_department_sum.to_excel(excel_writer, sheet_name="部门总费用聚合", index=False)


    #     # 产品总费用聚合
    #     bill_product_sum_datas = []
    #     last_infos = []
    #     for bill_cycle in bill_cycles:
    #         bill_product_sum_info = BillInstance.get_instance_aggregate(self.session, bill_cycle=bill_cycle, field="product_name", page=1, per_page=1000)
    #         self.get_diff_data(bill_product_sum_info["items"], last_infos, "product_name", "pretax_amount")
    #         bill_product_sum_datas.extend(bill_product_sum_info["items"])
    #         last_infos = bill_product_sum_info["items"]
    #     df_bill_product_sum = pd.DataFrame(bill_product_sum_datas)
    #     df_bill_product_sum.to_excel(excel_writer, sheet_name="产品总费用聚合", index=False)

    #     ########################################################
    #     # 部门变更费用聚合
    #     # bill_department_change_datas = []
    #     # for bill_cycle in bill_cycles:
    #     #     bill_department_change_info = BillAnalysis.get_analysis_aggregate(self.session, bill_cycle=bill_cycle, field="belong_department", page=1, per_page=1000)
    #     #     bill_department_change_datas.extend(bill_department_change_info["items"])
    #     # df_bill_department_change = pd.DataFrame(bill_department_change_datas)
    #     # df_bill_department_change.to_excel(excel_writer, sheet_name="部门变更费用聚合", index=False)


    #     # # 产品变更费用聚合
    #     # bill_product_change_datas = []
    #     # for bill_cycle in bill_cycles:
    #     #     bill_product_change_info = BillAnalysis.get_analysis_aggregate(self.session, bill_cycle=bill_cycle, field="product_name", page=1, per_page=1000)
    #     #     bill_product_change_datas.extend(bill_product_change_info["items"])
    #     # df_bill_product_change = pd.DataFrame(bill_product_change_datas)
    #     # df_bill_product_change.to_excel(excel_writer, sheet_name="产品变更费用聚合", index=False)
 
    #     ########################################################
    #     # 三大类
    #     bill_class_datas = []
    #     for bill_class in ["k8s集群", "数据库集群", "大数据实时集群", "大数据离线集群"]:
    #         pre_instance_cluster = {}
    #         for bill_cycle in bill_cycles:
    #             instance_cluster = BillInstance.get_instance_cluster(self.session, bill_cycle, bill_class, 1, 10000)
    #             if not instance_cluster:
    #                 continue 
    #             # for item in instance_cluster["items"]:
    #             #     item["ratio"] = round(item["pretax_amount"]/instance_cluster["total_amount"]*100, 2)
    #             #     item["change"] = 0
    #             #     item["change_ratio"] = 0
    #             #     if pre_instance_cluster:
    #             #         item["change"] = round(item["pretax_amount"] - pre_instance_cluster[item["belong_department"]]["pretax_amount"], 2)
    #             #         item["change_ratio"] = round(item["change"]/pre_instance_cluster[item["belong_department"]]["pretax_amount"]*100, 2)
    #             total_item = {
    #                 "bill_cycle": bill_cycle,
    #                 "bill_class": bill_class,
    #                 "belong_department": "total",
    #                 "pretax_amount": instance_cluster["total_amount"],
    #                 "change": 0,
    #                 "change_ratio": 0
    #             }
    #             if pre_instance_cluster and pre_instance_cluster["total"]["pretax_amount"]:
    #                 total_item["change"] = round(total_item["pretax_amount"] - pre_instance_cluster["total"]["pretax_amount"], 2)
    #                 total_item["change_ratio"] = round(total_item["change"]/pre_instance_cluster["total"]["pretax_amount"]*100, 2)
    #             instance_cluster["items"].append(total_item)
    #             # bill_class_datas.extend(instance_cluster["items"])
    #             bill_class_datas.append(total_item)
    #             pre_instance_cluster = {
    #                 item["belong_department"]: item
    #                 for item in instance_cluster["items"]
    #             }


    #     df_class_data = pd.DataFrame(bill_class_datas)
    #     df_class_data.to_excel(excel_writer, sheet_name="归类分析", index=False)


    #     excel_writer.close()
    #     logger.info("bill analysis report success")





    # def bill_event_from_work_order(self, bill_cycle:str):
    #     bill_event_handler = BillEventHandler()
    #     result = bill_event_handler.task_update_from_work_order_handle(self.session, bill_cycle)
    #     if not result:
    #         logger.error(f"sync bill event from work order failed")
    #         return False
        
    #     logger.info("sync bill event from work order success")
    #     return True


    # @classmethod
    # def db_import_data(cls):
    #     """
    #     导入初始化数据
    #     """
    #
    #     session = SessionLocal()
    #     for route in app.routes:
    #         if isinstance(route, APIRoute):
    #             if route.path in ["/ops/api/v1/login", "/ops/api/v1/user/info"]:
    #                 continue
    #             data = {"permission_name": route.name, "method": list(route.methods)[0].lower(),
    #                     "url": route.path, "tags": list(route.tags)[0].lower(),
    #                     "summary": route.summary, "description": route.description}
    #             AuthPermissions.saves(session, [data])
    #     session.close()
    #     return {"message": "ok!"}

